package com.pang.songbo.songboexcel.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel文件读取工具类
 * 支持.xls和.xlsx格式
 */
public class ExcelReader {
    
    /**
     * 读取Excel文件内容
     * @param filePath 文件路径
     * @param sheetIndex 工作表索引（从0开始）
     * @return 包含所有行数据的列表
     * @throws IOException
     */
    public static List<Map<String, Object>> readExcel(String filePath, int sheetIndex) throws IOException {
        List<Map<String, Object>> dataList = new ArrayList<>();
        FileInputStream inputStream = null;
        Workbook workbook = null;
        
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                throw new IOException("文件不存在: " + filePath);
            }
            
            inputStream = new FileInputStream(file);
            
            // 根据文件扩展名创建不同的Workbook对象
            if (filePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                throw new IOException("不支持的文件格式: " + filePath);
            }
            
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (sheet == null) {
                throw new IOException("工作表不存在，索引: " + sheetIndex);
            }
            
            // 获取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                return dataList;
            }
            
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(getCellValueAsString(cell));
            }
            
            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                
                Map<String, Object> rowData = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    String header = headers.get(j);
                    rowData.put(header, getCellValue(cell));
                }
                dataList.add(rowData);
            }
            
        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }
        
        return dataList;
    }


    public static List<Map<String, Object>> readExcelStream(InputStream inputStream, String filePath, int sheetIndex) throws IOException {
        List<Map<String, Object>> dataList = new ArrayList<>();

        Workbook workbook = null;

        try {

            // 根据文件扩展名创建不同的Workbook对象
            if (filePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                throw new IOException("不支持的文件格式: " + filePath);
            }

            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (sheet == null) {
                throw new IOException("工作表不存在，索引: " + sheetIndex);
            }

            // 获取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                return dataList;
            }

            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(getCellValueAsString(cell));
            }

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                Map<String, Object> rowData = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    String header = headers.get(j);
                    rowData.put(header, getCellValue(cell));
                }
                dataList.add(rowData);
            }

        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }

        return dataList;
    }
    
    /**
     * 读取指定工作表的所有数据
     * @param filePath 文件路径
     * @param sheetName 工作表名称
     * @return 包含所有行数据的列表
     * @throws IOException
     */
    public static List<Map<String, Object>> readExcel(String filePath, String sheetName) throws IOException {
        FileInputStream inputStream = null;
        Workbook workbook = null;
        
        try {
            File file = new File(filePath);
            inputStream = new FileInputStream(file);
            
            if (filePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                throw new IOException("不支持的文件格式: " + filePath);
            }
            
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                throw new IOException("工作表不存在，名称: " + sheetName);
            }
            
            return readExcel(filePath, workbook.getSheetIndex(sheet));
            
        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }
    
    /**
     * 获取单元格的值
     * @param cell 单元格对象
     * @return 单元格的值
     */
    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
    
    /**
     * 获取单元格的字符串值
     * @param cell 单元格对象
     * @return 单元格的字符串表示
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
    
    /**
     * 读取所有工作表
     * @param filePath 文件路径
     * @return 包含所有工作表数据的Map
     * @throws IOException
     */
    public static Map<String, List<Map<String, Object>>> readAllSheets(String filePath) throws IOException {
        Map<String, List<Map<String, Object>>> allData = new HashMap<>();
        FileInputStream inputStream = null;
        Workbook workbook = null;
        
        try {
            File file = new File(filePath);
            inputStream = new FileInputStream(file);
            
            if (filePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                throw new IOException("不支持的文件格式: " + filePath);
            }
            
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                allData.put(sheetName, readExcel(filePath, i));
            }
            
        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }
        
        return allData;
    }
    
    /**
     * 测试方法
     */
    public static void main(String[] args) {
        try {
            String filePath = "test.xlsx"; // 替换为您的Excel文件路径
            
            // 读取第一个工作表
            List<Map<String, Object>> data = readExcel(filePath, 0);
            
            // 打印读取的数据
            for (Map<String, Object> row : data) {
                System.out.println(row);
            }
            
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
